<?php
require_once dirname(__FILE__) . '/../includes/common.inc.php';

header('Content-Type: text/plain');

$tableProduce = 'ns_Produce';
$tableProduceEvent = 'ns_ProduceEvent';
$tableProduceTrack = 'ns_ProduceTrack';
$tableProduceUser = 'ns_ProduceUser';
$tableProduceTrackUser = 'ns_ProduceTrackUser';
$tableProducePurchaseItem = 'ns_ProducePurchaseItem';
$tableProducePurchaseItemSelect = 'ns_ProducePurchaseItemSelect';
$tableProduceSelect = 'ns_ProduceSelect';
$tableProduceNote = 'ns_ProduceNote';
$tableParcels = 'ns_parcels';
//$branch = 'NYC';


try {
  
  $hostname = HOST_NAME;
  $username = USER_NAME2;
  $password = PASSWORD2;
  $dbname = DATABASE_NAME2;
  $myPdo = new PDO ('mysql:host=' . $hostname, $username, $password);
  $myPdo->exec('USE ' . $dbname);
  $myPdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
  echo $e->getMessage ();
}

$action = MiscUtils::getParam('action', NULL);
try {
  switch ($action) {
    case 'produce':
      produce($myPdo);
      break;
      
    case 'produce2':
      produce2($myPdo);
      break;
      
    case 'parcels':
      parcels($myPdo);
      break;
      
    default:
      break;
  }
} catch (PDOException $e) {
  echo $e->getMessage();
}

function produce($myPdo) {
  global $branch,$tableProduce, $tableProduceEvent, $tableProduceTrack, $tableProduceTrackUser, $tableProduceUser, $tableProducePurchaseItem, $tableProducePurchaseItemSelect, $tableProduceSelect, $tableProduceNote;
  
  $function = MiscUtils::getParam('f', NULL);
  $condition = MiscUtils::getParam('c', '1 = 1');
  $order = MiscUtils::getParam('o', 'd.deadline');
  $queue = MiscUtils::getParam('q', 'ASC');
  $page = MiscUtils::getParam('p', START);
  $size = MiscUtils::getParam('s', NULL);
  $pageSkip = ($page - 1) * $size;

  $con = MiscUtils::getParam('line', NULL);
  
  if ($con == 1) {
    $now = SimpleDate::fromStamp(time() + LAG);
    $now->hours = 0;
    $now->minutes = 0;
    $now->seconds = 0;
    $deadline = (SimpleDate::toStamp($now) - 259200);
    $condition .= ' AND (d.deadline < ' . $deadline . ')';
  }
  
  //$condition .= " AND d.branch = '".$branch."'";
  
  
  $hasSelects = MiscUtils::getParam('selects', NULL);
  $hasPurchaseItems = MiscUtils::getParam('purchaseitems', NULL);
  $hasPreconUsers = MiscUtils::getParam('preconusers', NULL);
  $hasEvents = MiscUtils::getParam('events', NULL);
  $hasTracks = MiscUtils::getParam('tracks', NULL);
  $hasProduces = MiscUtils::getParam('produces', NULL);
  $hasNotes = MiscUtils::getParam('notes', NULL);
  
  $sql = "SELECT * FROM $tableProduce as d ";
  $sql .= (($condition) ? "WHERE $condition " : '') . (($order && $queue) ? "ORDER BY $order $queue " : '') . (($page && $size) ? " LIMIT " . $pageSkip . ", $size" : '');
  
  $stmt = $myPdo->prepare($sql);
  $stmt->execute();
  
  $result = new stdClass();
  $result->data = array();
  $result->page = $page;
  $result->size = $size;
  $result->order = $order;
  $result->queue = $queue;
  $result->condition = $condition;
  
  if ($page && $size) {
    $sql1 = substr($sql, 0, strrpos($sql, 'LIMIT'));
    $stmt1 = $myPdo->prepare($sql1);
    $stmt1->execute();
    $result->total = $stmt1->rowCount();
  } else {
    $result->total = $stmt->rowCount();
  }
  
  $i = 0;
  while ($i < $stmt->rowCount()) {
    $tmp = $stmt->fetch(PDO::FETCH_OBJ);
    $tmp->id = $tmp->e_oid;
    $tmp->createdDate = ($tmp->createdDate != 0) ? SimpleDate::fromStamp($tmp->createdDate) : NULL;
    $tmp->deadline = ($tmp->deadline != 0) ? SimpleDate::fromStamp($tmp->deadline) : 0;
    $tmp->sentDate = ($tmp->sentDate != 0) ? SimpleDate::fromStamp($tmp->sentDate) : NULL;
    $tmp->receivedDate = ($tmp->receivedDate != 0) ? SimpleDate::fromStamp($tmp->receivedDate) : NULL;
    $tmp->when = ($tmp->when != 0) ? SimpleDate::fromStamp($tmp->when) : NULL;
    $tmp->date = ($tmp->date != 0) ? SimpleDate::fromStamp($tmp->date) : NULL;
    
    $tmp->pi_selects = array();
    if ($hasSelects) {
      $sql2 = "SELECT * FROM $tableProduceSelect AS s WHERE s.produce_trackId = '$tmp->trackId'";
      $stmt2 = $myPdo->prepare($sql2);
      $stmt2->execute();
      $j = 0;
      while ($j < $stmt2->rowCount()) {
        $tmp2 = $stmt2->fetch(PDO::FETCH_OBJ);
        $tmp2->id = $tmp2->e_oid;
        $tmp->pi_selects[] = $tmp2;
        $j++;
      }
    }
    
    $tmp->v2_purchaseItems = array();
    if ($hasPurchaseItems) {
      $sql3 = "SELECT * FROM $tableProducePurchaseItem AS pi WHERE pi.produce_trackId = '$tmp->trackId'";
      $stmt3 = $myPdo->prepare($sql3);
      $stmt3->execute();
      $j = 0;
      while ($j < $stmt3->rowCount()) {
        $tmp3 = $stmt3->fetch(PDO::FETCH_OBJ);
        $tmp3->id = $tmp3->e_oid;
        $tmp3->v2_selects = array();
        $sql31 = "SELECT DISTINCT ps.e_oid as id, ps.title, ps.value FROM $tableProducePurchaseItemSelect AS ps WHERE ps.purchase_trackId = '$tmp3->trackId'";
        $stmt31 = $myPdo->prepare($sql31);
        $stmt31->execute();
        $k = 0;
        while ($k < $stmt31->rowCount()) {
          $tmp31 = $stmt31->fetch(PDO::FETCH_OBJ);
          $tmp3->v2_selects[] = $tmp31;
          $k++;
        }
        $tmp->v2_purchaseItems[] = $tmp3;
        $j++;
      }
    }
    
    $tmp->pUsers = array();
    if ($hasPreconUsers) {
      $sql4 = "SELECT * FROM $tableProduceUser AS u WHERE u.produce_id = $tmp->id";
      $stmt4 = $myPdo->prepare($sql4);
      $stmt4->execute();
      $j = 0;
      while ($j < $stmt4->rowCount()) {
        $tmp4 = $stmt4->fetch(PDO::FETCH_OBJ);
        $tmp4->id = $tmp4->e_oid;
        $tmp->pUsers[] = $tmp4;
        $j++;
      }
    }
    
    $tmp->v2_events = array();
    if ($hasEvents) {
      $sql5 = "SELECT * FROM $tableProduceEvent AS e WHERE e.produce_trackId = '$tmp->trackId'";
      $stmt5 = $myPdo->prepare($sql5);
      $stmt5->execute();
      $j = 0;
      while ($j < $stmt5->rowCount()) {
        $tmp5 = $stmt5->fetch(PDO::FETCH_OBJ);
        $tmp5->id = $tmp5->e_oid;
        $tmp5->date = ($tmp5->date != 0) ? SimpleDate::fromStamp($tmp5->date) : NULL;
        $tmp->v2_events[] = $tmp5;
        $j++;
      }
    }
    
    $tmp->v2_tracks = array();
    if ($hasTracks) {
      $sql6 = "SELECT * FROM $tableProduceTrack AS t WHERE t.produce_id = $tmp->id";
      $stmt6 = $myPdo->prepare($sql6);
      $stmt6->execute();
      $j = 0;
      while ($j < $stmt6->rowCount()) {
        $tmp6 = $stmt6->fetch(PDO::FETCH_OBJ);
        $tmp6->id = $tmp6->e_oid;
        $tmp6->createdDate = ($tmp6->createdDate != 0) ? SimpleDate::fromStamp($tmp6->createdDate) : NULL;
        $tmp6->deadline = ($tmp6->deadline != 0) ? SimpleDate::fromStamp($tmp6->deadline) : NULL;
        $tmp6->sentDate = ($tmp6->sentDate != 0) ? SimpleDate::fromStamp($tmp6->sentDate) : NULL;
        $tmp6->receivedDate = ($tmp6->receivedDate != 0) ? SimpleDate::fromStamp($tmp6->receivedDate) : NULL;
        $tmp6->when = ($tmp6->when != 0) ? SimpleDate::fromStamp($tmp6->when) : NULL;
        $tmp6->date = ($tmp6->date != 0) ? SimpleDate::fromStamp($tmp6->date) : NULL;
        
        $tmp6->pUsers = array();
        $sql61 = "SELECT * FROM $tableProduceTrackUser AS u WHERE u.track_trackId = '$tmp6->trackId'";
        $stmt61 = $myPdo->prepare($sql61);
        $stmt61->execute();
        $k = 0;
        while ($k < $stmt61->rowCount()) {
          $tmp61 = $stmt61->fetch(PDO::FETCH_OBJ);
          $tmp61->id = $tmp61->e_oid;
          $tmp6->pUsers[] = $tmp61;
          $k++;
        }
        $tmp->v2_tracks[] = $tmp6;
        $j++;
      }
    }
    
    $tmp->v2_produces = array();
    if ($hasProduces) {
      $sql7 = "SELECT DISTINCT * FROM $tableProduce AS pr WHERE pr.parent_trackId = '$tmp->trackId'";
      $stmt7 = $myPdo->prepare($sql7);
      $stmt7->execute();
      $j = 0;
      while ($j < $stmt7->rowCount()) {
        $tmp7 = $stmt7->fetch(PDO::FETCH_OBJ);
        $tmp7->id = $tmp7->e_oid;
        $tmp7->createdDate = ($tmp7->createdDate != 0) ? SimpleDate::fromStamp($tmp7->createdDate) : NULL;
        $tmp7->deadline = ($tmp7->deadline != 0) ? SimpleDate::fromStamp($tmp7->deadline) : NULL;
        $tmp7->sentDate = ($tmp7->sentDate != 0) ? SimpleDate::fromStamp($tmp7->sentDate) : NULL;
        $tmp7->receivedDate = ($tmp7->receivedDate != 0) ? SimpleDate::fromStamp($tmp7->receivedDate) : NULL;
        $tmp7->when = ($tmp7->when != 0) ? SimpleDate::fromStamp($tmp7->when) : NULL;
        $tmp7->date = ($tmp7->date != 0) ? SimpleDate::fromStamp($tmp7->date) : NULL;
        
        $tmp7->pi_selects = array();
        $sql71 = "SELECT * FROM $tableProduceSelect AS s WHERE s.produce_trackId = '$tmp7->trackId'";
        $stmt71 = $myPdo->prepare($sql71);
        $stmt71->execute();
        $k = 0;
        while ($k < $stmt71->rowCount()) {
          $tmp71 = $stmt71->fetch(PDO::FETCH_OBJ);
          $tmp71->id = $tmp71->e_oid;
          $tmp7->pi_selects[] = $tmp71;
          $k++;
        }
        
        $tmp7->v2_purchaseItems = array();
        $sql72 = "SELECT * FROM $tableProducePurchaseItem AS pi WHERE pi.produce_trackId = '$tmp7->trackId'";
        $stmt72 = $myPdo->prepare($sql72);
        $stmt72->execute();
        $l = 0;
        while ($l < $stmt72->rowCount()) {
          $tmp72 = $stmt72->fetch(PDO::FETCH_OBJ);
          $tmp72->id = $tmp72->e_oid;
          $tmp72->v2_selects = array();
          $sql721 = "SELECT DISTINCT ps.e_oid as id, ps.title, ps.value FROM $tableProducePurchaseItemSelect AS ps WHERE ps.purchase_trackId = '$tmp72->trackId'";
          $stmt721 = $myPdo->prepare($sql721);
          $stmt721->execute();
          $m = 0;
          while ($m < $stmt721->rowCount()) {
            $tmp721 = $stmt721->fetch(PDO::FETCH_OBJ);
            $tmp72->v2_selects[] = $tmp721;
            $m++;
          }
          $tmp7->v2_purchaseItems[] = $tmp72;
          $l++;
        }
        $tmp->v2_produces[] = $tmp7;
        $j++;
      }
    }
    if ($hasNotes) {
      $tmp->v2_notes = array();
      $sql8 = "SELECT * FROM $tableProduceNote AS n WHERE n.produce_trackId = '$tmp->trackId'";
      $stmt8 = $myPdo->prepare($sql8);
      $stmt8->execute();
      $j = 0;
      while ($j < $stmt8->rowCount()) {
        $tmp8 = $stmt8->fetch(PDO::FETCH_OBJ);
        $tmp8->id = $tmp8->e_oid;
        $tmp8->date = ($tmp8->date != 0) ? SimpleDate::fromStamp($tmp8->date) : NULL;
        $tmp->v2_notes[] = $tmp8;
        $j++;
      }
    }
    $result->data[] = $tmp;
    $i++;
  }
  echo json_encode($result);
}


function produce2($myPdo) {
  global $branch,$tableProduce, $tableProduceEvent, $tableProduceTrack, $tableProduceTrackUser, $tableProduceUser, $tableProducePurchaseItem, $tableProducePurchaseItemSelect, $tableProduceSelect, $tableProduceNote;
  
  $function = MiscUtils::getParam('f', NULL);
  $condition = MiscUtils::getParam('c', '1 = 1');
  $order = MiscUtils::getParam('o', 'd.deadline');
  $queue = MiscUtils::getParam('q', 'ASC');
  $page = MiscUtils::getParam('p', START);
  $size = MiscUtils::getParam('s', NULL);
  $pageSkip = ($page - 1) * $size;

  $sql = "SELECT DISTINCT d.e_oid, d.* FROM $tableProduce as d LEFT JOIN ns_produce AS d2 ON d.trackId = d2.parent_trackid ";
  $sql .= (($condition) ? "WHERE $condition " : '') . (($order && $queue) ? "ORDER BY $order $queue " : '') . (($page && $size) ? " LIMIT " . $pageSkip . ", $size" : '');
  $stmt = $myPdo->prepare($sql);
  $stmt->execute();
  
  $result = new stdClass();
  $result->data = array();
  $result->page = $page;
  $result->size = $size;
  $result->order = $order;
  $result->queue = $queue;
  $result->condition = $condition;
  
  if ($page && $size) {
    $sql1 = substr($sql, 0, strrpos($sql, 'LIMIT'));
    $stmt1 = $myPdo->prepare($sql1);
    $stmt1->execute();
    $result->total = $stmt1->rowCount();
  } else {
    $result->total = $stmt->rowCount();
  }
  
  $i = 0;
  while ($i < $stmt->rowCount()) {
    $tmp = $stmt->fetch(PDO::FETCH_OBJ);
    $tmp->id = $tmp->e_oid;
    $tmp->createdDate = ($tmp->createdDate != 0) ? SimpleDate::fromStamp($tmp->createdDate) : NULL;
    $tmp->deadline = ($tmp->deadline != 0) ? SimpleDate::fromStamp($tmp->deadline) : NULL;
    $tmp->sentDate = ($tmp->sentDate != 0) ? SimpleDate::fromStamp($tmp->sentDate) : NULL;
    $tmp->receivedDate = ($tmp->receivedDate != 0) ? SimpleDate::fromStamp($tmp->receivedDate) : NULL;
    $tmp->when = ($tmp->when != 0) ? SimpleDate::fromStamp($tmp->when) : NULL;
    $tmp->date = ($tmp->date != 0) ? SimpleDate::fromStamp($tmp->date) : NULL;
    
    $tmp->pi_selects = array();
    $sql2 = "SELECT * FROM $tableProduceSelect AS s WHERE s.produce_trackId = '$tmp->trackId'";
    $stmt2 = $myPdo->prepare($sql2);
    $stmt2->execute();
    $j = 0;
    while ($j < $stmt2->rowCount()) {
      $tmp2 = $stmt2->fetch(PDO::FETCH_OBJ);
      $tmp2->id = $tmp2->e_oid;
      $tmp->pi_selects[] = $tmp2;
      $j++;
    }
  
    $tmp->v2_notes = array();
    $sql8 = "SELECT * FROM $tableProduceNote AS n WHERE n.produce_trackId = '$tmp->trackId'";
    $stmt8 = $myPdo->prepare($sql8);
    $stmt8->execute();
    $j = 0;
    while ($j < $stmt8->rowCount()) {
      $tmp8 = $stmt8->fetch(PDO::FETCH_OBJ);
      $tmp8->id = $tmp8->e_oid;
      $tmp8->date = ($tmp8->date != 0) ? SimpleDate::fromStamp($tmp8->date) : NULL;
      $tmp->v2_notes[] = $tmp8;
      $j++;
    }
    $result->data[] = $tmp;
    $i++;
  }
  echo json_encode($result);
}

/*
 * 非产品类邮寄功能
 * 
 */
function parcels($myPdo) {
  global $branch,$tableParcels;
  $condition = MiscUtils::getParam('c', '1 = 1');
  $order = MiscUtils::getParam('o', 'd.createdDate');
  $queue = MiscUtils::getParam('q', 'ASC');
  $page = MiscUtils::getParam('p', START);
  $size = MiscUtils::getParam('s', NULL);
  $pageSkip = ($page - 1) * $size;

  $sql = "SELECT DISTINCT d.e_oid, d.* FROM $tableParcels AS d ";
  $sql .= (($condition) ? "WHERE $condition " : '') . (($order && $queue) ? "ORDER BY $order $queue " : '') . (($page && $size) ? " LIMIT " . $pageSkip . ", $size" : '');
  
  $stmt = $myPdo->prepare($sql);
  $stmt->execute();
  
  $result = new stdClass();
  $result->data = array();
  $result->page = $page;
  $result->size = $size;
  $result->order = $order;
  $result->queue = $queue;
  $result->condition = $condition;
  
  if ($page && $size) {
    $sql1 = substr($sql, 0, strrpos($sql, 'LIMIT'));
    $stmt1 = $myPdo->prepare($sql1);
    $stmt1->execute();
    $result->total = $stmt1->rowCount();
  } else {
    $result->total = $stmt->rowCount();
  }
  
  $i = 0;
  while ($i < $stmt->rowCount()) {
    $tmp = $stmt->fetch(PDO::FETCH_OBJ);
    $tmp->id = $tmp->e_oid;
    $tmp->createdDate = ($tmp->createdDate != 0) ? SimpleDate::fromStamp($tmp->createdDate) : NULL;
    $tmp->receivedDate = ($tmp->receivedDate != 0) ? SimpleDate::fromStamp($tmp->receivedDate) : NULL;
    
    $result->data[] = $tmp;
    $i++;
  }
  echo json_encode($result);
}

?>